-- CREACIÓN BASE DE DATOS
CREATE DATABASE AEROLINEAS;

USE AEROLINEAS;

-- CREACIÓN DE TABLAS
CREATE TABLE AEROPUERTOS (
    ID_AEROP INT NOT NULL,
    NOM_AEROP VARCHAR(50) NOT NULL,
    CIUDAD VARCHAR(50) NOT NULL,
    PAIS VARCHAR (50) NOT NULL
);

CREATE TABLE RUTAS (
    NRO_RUTA INT NOT NULL,
    DISTANCIA DECIMAL(9,2) NOT  NULL,
    ID_AEROP_ORIGEN INT NOT NULL,
    ID_AEROP_DESTINO INT NOT NULL
);
    
CREATE TABLE VUELOS(
    ID_VUELO INT NOT NULL,
    EMPRESA VARCHAR(65) NOT NULL
);

CREATE TABLE RDVUELOS(
    ID_VUELO INT NOT NULL,
    NRO_RUTA INT NOT NULL,
    ALTITUD DECIMAL(9,2) NOT NULL
);

-- ADICIÓN CLAVES PRIMARIAS
ALTER TABLE AEROPUERTOS
ADD CONSTRAINT PK_ID_AEROP PRIMARY KEY (ID_AEROP);

ALTER TABLE RUTAS
ADD CONSTRAINT PK_NRO_RUTA PRIMARY KEY (NRO_RUTA);

ALTER TABLE VUELOS
ADD CONSTRAINT PK_ID_VUELO PRIMARY KEY (ID_VUELO);

ALTER TABLE RDVUELOS
ADD CONSTRAINT PK_ID_VUELO_NRO_RUTA PRIMARY KEY (ID_VUELO, NRO_RUTA);

-- ADICIÓN CLAVES FORÁNEAS

ALTER TABLE RUTAS
ADD CONSTRAINT FK_ID_AEROP_ORIGEN FOREIGN KEY (ID_AEROP_ORIGEN) 
REFERENCES AEROPUERTOS(ID_AEROP),
ADD CONSTRAINT FK_ID_AEROP_DESTINO FOREIGN KEY (ID_AEROP_DESTINO)
REFERENCES AEROPUERTOS(ID_AEROP);

ALTER TABLE RDVUELOS
ADD CONSTRAINT FK_ID_VUELO FOREIGN KEY (ID_VUELO) REFERENCES VUELOS(ID_VUELO),
ADD CONSTRAINT FK_NRO_RUTA FOREIGN KEY (NRO_RUTA) REFERENCES RUTAS(NRO_RUTA);

-- POPULATE BD (ADICIÓN DE DATOS)

-- AEROPUERTOS
INSERT INTO AEROPUERTOS
VALUES(1, 'AEROPUERTO INTERNACIONAL DE CARRASCO', 'CANELONES', 'URUGUAY');
INSERT INTO AEROPUERTOS
VALUES(2,'AEROPUERTO INTERNACIONAL EZEIZA', 'BS. AS.', 'ARGENTINA');
INSERT INTO AEROPUERTOS
VALUES(3, 'AEROPUERTO INTERNACIONAL SALGADO FILHO', 'PORTO ALEGRE', 'BRASIL');
INSERT INTO AEROPUERTOS
VALUES(4, 'AEROPUERTO INTERNACIONAL JOHN F. KENNEDY', 'NEW YORK', 'USA');
INSERT INTO AEROPUERTOS
VALUES(5, 'AEROPUERTO INTERNACIONAL DE LOS ÁNGELES', 'LOS ÁNGELES CA', 'USA');
INSERT INTO AEROPUERTOS
VALUES(6, 'AEROPUERTO INT. MADRID-BARAJAS ADOLFO SUÁREZ', 'MADRID', 'ESPAÑA');
INSERT INTO AEROPUERTOS
VALUES(7, 'AEROPUERTO DE ROMA-FIUMICINO', 'ROMA', 'ITALIA');
INSERT INTO AEROPUERTOS
VALUES(8, 'AEROPUERTO INTERNACIONAL DE MOSCOW', 'MOSCOW', 'RUSIA');
INSERT INTO AEROPUERTOS
VALUES(9, 'AEROPUERTO INTERNACIONAL PÚLKOVO', 'ST. PETERSBURG', 'RUSIA');
INSERT INTO AEROPUERTOS
VALUES(10, 'AEROPUERTO INTERNACIONAL BENITO JUÁREZ', 'CIUDAD DE MÉXICO', 'MÉXICO');
INSERT INTO AEROPUERTOS
VALUES(11, 'AEROPUERTO INTERNACIONAL DE DENVER', 'DENVER', 'USA');
INSERT INTO AEROPUERTOS
VALUES(12, 'AEROPUERTO INTERNACIONAL DE SAO-PAULO', 'SAO PAULO', 'BRASIL');
INSERT INTO AEROPUERTOS
VALUES(13, 'AEROPUERTO DE ESTOCOLMO-ARLANDA', 'ESTOCOLMO', 'SUECIA');
INSERT INTO AEROPUERTOS
VALUES(14, 'AEROPUERTO DE GOTEMBURGO-LANDVETTER', 'LANDVETTER', 'SUECIA');
INSERT INTO AEROPUERTOS
VALUES(15, 'AEROPUERTO DE MALINDI', 'MALINDI', 'KENIA');
INSERT INTO AEROPUERTOS
VALUES(16, 'AEROPUERTO DE HAIFA', 'HAIFA', 'ISRAEL');
INSERT INTO AEROPUERTOS 
VALUES(17, 'AEROPUERTO INTERNACIONAL ÁNGEL ADAMI', 'MONTEVIDEO', 'URUGUAY' );
INSERT INTO AEROPUERTOS 
VALUES (18, 'AEROPUERTO INTERNACIONAL JORGE CHÁVEZ', 'LIMA', 'PERU');

-- RUTAS DISTANCIAS DEFINIDAS EN KM

-- RUTA AEROPUERTO CARRASCO A SALGADO FILHO BRASIL
INSERT INTO RUTAS
VALUES(136, 768.1, 1, 3);
-- RUTA DE AEROP. SALGADO FILHO A J.F. KENNEDY
INSERT INTO RUTAS
VALUES(209, 6912.9, 3, 4);
-- RUTA DE AEROP. J.F. KENNEDY A LOS ÁNGELES
INSERT INTO RUTAS
VALUES(53, 4488.4, 4, 5);
-- RUTA DE AEROP. C. DE MÉXICO A ROMA ITALIA
INSERT INTO RUTAS
VALUES(667, 5553.9, 10, 7);
-- RUTA DE MOSCÚ A ESPAÑA 
INSERT INTO RUTAS
VALUES(333, 4099.8, 8, 6);
-- RUTA DE MONTEVIDEO A C. DE MÉXICO
INSERT INTO RUTAS
VALUES(57, 7972.7, 1, 10);
-- RUTA DE BRASIL A ESPAÑA  
INSERT INTO RUTAS
VALUES(88, 7845.30, 3, 6);
-- RUTA DE ARGENTINA A ITALIA
INSERT INTO RUTAS
VALUES(98, 11778.6, 2, 7);
-- RUTA DE LOS ÁNGELES A ESPAÑA
INSERT INTO RUTAS
VALUES(898, 9350.2, 5, 6);
-- RUTA DESDE PÚLKOVO A ITALIA
INSERT INTO RUTAS
VALUES(121, 5894.2, 9, 7);
-- RUTA DESDE MONTEVIDEO A DENVER USA
INSERT INTO RUTAS
VALUES(2300, 9600.9, 1, 11);
-- RUTA DESDE ESPAÑA A BRASIL 
INSERT INTO RUTAS
VALUES(251, 7845.8, 6, 12);
-- RUTA DESDE ARGENTINA A DENVER USA 
INSERT INTO RUTAS
VALUES(2301, 9578.5, 2, 11);
-- RUTA DESDE MELILLA MONTEVIDEO A BRASIL
INSERT INTO RUTAS 
VALUES (331, 1950.6, 17, 3);
-- RUTA DESDE MELILLA MONTEVIDEO A DENVER USA
INSERT INTO RUTAS
VALUES (345, 9620.13, 17, 11);
-- RUTA DESDE MELILLA MONTEVIDEO A BRASIL
INSERT INTO RUTAS 
VALUES (344, 1944.5, 17, 12);

-- POPULATE VUELOS
INSERT INTO VUELOS VALUES(200, 'AIR CLASS');
INSERT INTO VUELOS VALUES(201, 'AMASZONAS');
INSERT INTO VUELOS VALUES(302, 'AEROLÍNEAS ARGENTINAS');
INSERT INTO VUELOS VALUES(404, 'LATAM');
INSERT INTO VUELOS VALUES(405, 'AVIANCA BRAZIL');
INSERT INTO VUELOS VALUES(506, 'IBERIA');
INSERT INTO VUELOS VALUES(607, 'AIR ITALY');
INSERT INTO VUELOS VALUES(708, 'AEROFLOT RUSSIAN AIRLINES');
INSERT INTO VUELOS VALUES(709, 'NORDAVIA');
INSERT INTO VUELOS VALUES(810, 'AEROMAR');
INSERT INTO VUELOS VALUES(811, 'AEROMÉXICO');
INSERT INTO VUELOS VALUES(912, 'ALLEGIANT AIR');
INSERT INTO VUELOS VALUES(913, 'SPIRIT AIRLINES');
INSERT INTO VUELOS VALUES(914, 'AMERICAN AIRLINES');
INSERT INTO VUELOS VALUES(915, 'JETBLUE');
INSERT INTO VUELOS VALUES(916, 'UNITED AIRLINES');

-- POPULATE RUTA DE VUELOS EN METROS
INSERT INTO RDVUELOS VALUES(201, 136, 10880.9);
INSERT INTO RDVUELOS VALUES(302, 136, 8780.1);
INSERT INTO RDVUELOS VALUES(201, 331, 8100.12);
INSERT INTO RDVUELOS VALUES(404, 209, 12000.1);
INSERT INTO RDVUELOS VALUES(914, 53, 9506.2);
INSERT INTO RDVUELOS VALUES(200, 53, 8607.1);
INSERT INTO RDVUELOS VALUES(709, 53, 10680.4);
INSERT INTO RDVUELOS VALUES(811, 667, 14357.1);
INSERT INTO RDVUELOS VALUES(915, 667, 12337.5);
INSERT INTO RDVUELOS VALUES(405, 667, 10134.5);
INSERT INTO RDVUELOS VALUES(709, 333, 6440.3);
INSERT INTO RDVUELOS VALUES(200, 57, 10980.3);
INSERT INTO RDVUELOS VALUES(506, 88, 12011.9);
INSERT INTO RDVUELOS VALUES(607, 98, 14281.1);
INSERT INTO RDVUELOS VALUES(914, 898, 5809.5);
INSERT INTO RDVUELOS VALUES(708, 121, 8968.5);
INSERT INTO RDVUELOS VALUES(916, 2300, 9100.2);
INSERT INTO RDVUELOS VALUES(607, 2300, 7820.5);
INSERT INTO RDVUELOS VALUES(810, 2300, 9820.5);
INSERT INTO RDVUELOS VALUES(200, 2300, 6820.5);
INSERT INTO RDVUELOS VALUES(709, 2300, 5658.7);
INSERT INTO RDVUELOS VALUES (404, 251, 7890.33);
INSERT INTO RDVUELOS VALUES (200, 345, 6890.33);
INSERT INTO RDVUELOS VALUES (200, 344, 5400.60);

/* Pregunta 1 */
select AVG (ALTITUD) FROM RDVUELOS WHERE NRO_RUTA = 2300

/* Pregunta 2 (?) */

Select A.* From aeropuertos A INNER JOIN RUTAS R ON R.ID_AEROP_ORIGEN = A.ID_AEROP OR R.ID_AEROP_DESTINO = A.ID_AEROP
where A.ID_AEROP = NULL

/* Pregunta 3 */
SELECT V.EMPRESA FROM VUELOS V INNER JOIN RDVUELOS RD ON V.ID_VUELO = RD.ID_VUELO 
INNER JOIN RUTAS R ON RD.NRO_RUTA = R.NRO_RUTA 
INNER JOIN AEROPUERTOS A ON R.ID_AEROP_ORIGEN = A.ID_AEROP OR R.ID_AEROP_DESTINO = A.ID_AEROP
WHERE 
A.PAIS = 'BRASIL'

/* Pregunta 4 */
Select COUNT(NRO_RUTA) AS Cantidad_de_vuelos FROM RUTAS
WHERE (ID_AEROP_ORIGEN = 1
AND ID_AEROP_DESTINO = 3)
OR (ID_AEROP_ORIGEN = 1
AND ID_AEROP_DESTINO = 12)
OR (ID_AEROP_ORIGEN = 17
AND ID_AEROP_DESTINO = 3)
OR (ID_AEROP_ORIGEN = 17
AND ID_AEROP_DESTINO = 12)

/* Pregunta 5 */
Select V.ID_VUELO FROM VUELOS V INNER JOIN RDVUELOS RD ON V.ID_VUELO = RD.ID_VUELO 
INNER JOIN RUTAS R ON RD.NRO_RUTA = R.NRO_RUTA 
INNER JOIN AEROPUERTOS A ON R.ID_AEROP_ORIGEN = A.ID_AEROP OR R.ID_AEROP_DESTINO = A.ID_AEROP
WHERE (R.ID_AEROP_ORIGEN = 1
AND R.ID_AEROP_DESTINO = 11
or R.ID_AEROP_ORIGEN = 17
AND R.ID_AEROP_DESTINO = 11)
AND V.EMPRESA = 'UNITED AIRLINES'
AND RD.ALTITUD > 8000